﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Lead2012.Models;
using Lead2012.ViewModels;
using Lead2012.Helpers;
using Lead2012.Business;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace Lead2012.Repository
{
    public class BaseManagementRepository
    {
        // Create an instance of entities framework
        Lead2012Entities entities = new Lead2012Entities();
        
        public List<ItemFilter> GetStatuses()
        {
            var items = new LeadStatusRepository().GetAll();
            List<ItemFilter> list = new List<ItemFilter>();

            list.Add(new ItemFilter { ID = "", ItemName = "Tất cả tình trạng" });

            // Add item to list
            foreach (var item in items.OrderBy(c=>c.OrderId))
            {
                ItemFilter model = new ItemFilter();
                model.ID = item.StatusId.ToString();
                model.ItemName = item.Title;
                model.CssName = item.Color;
                list.Add(model);
            }

            return list;
        }

        public List<ItemFilter> GetLeadSource()
        {
            var items = new LeadSourceRepository().GetAll();
            List<ItemFilter> list = new List<ItemFilter>();

            list.Add(new ItemFilter { ID = "", ItemName = "Tất cả nguồn khách hàng" });

            // Add item to list
            foreach (var item in items)
            {
                ItemFilter model = new ItemFilter();
                model.ID = item.SourceId.ToString();
                model.ItemName = item.Title;
                list.Add(model);
            }

            return list;
        }

        public List<ItemFilter> GetLeadType()
        {
            var items = new LeadFormTypeRepository().GetAll();
            List<ItemFilter> list = new List<ItemFilter>();

            list.Add(new ItemFilter { ID = "", ItemName = "Tất cả loại khách hàng" });

            // Add item to list
            foreach (var item in items)
            {
                ItemFilter model = new ItemFilter();
                model.ID = item.TypeId.ToString();
                model.ItemName = item.Title;
                list.Add(model);
            }

            return list;
        }

        public List<ItemFilter> GetAllUsers()
        {
            List<ItemFilter> listAgent = new List<ItemFilter>();
            var results = new UserRepository().GetAll();

            foreach (var item in results)
            {
                ItemFilter model = new ItemFilter();
                model.ID = item.ID.ToString();
                model.ItemName = string.Format("{0}, {1}", item.FirstName, item.LastName);
                listAgent.Add(model);
            }

            return listAgent;
        }

        public List<ItemFilter> GetRangeFilter()
        {
            List<ItemFilter> listRange = new List<ItemFilter>();

            string ToDay = String.Format("{0:dd/MM/yyyy}", DateTime.Now);
            string Yesterday = DateTime.Now.AddDays(-1).ToString("dd/MM/yyyy");
            string today = ToDay + "|" + ToDay;
            string yesterday = Yesterday + "|" + Yesterday;
            string sLast7Days = DateTime.Now.AddDays(-6).ToString("dd/MM/yyyy") + "|" + ToDay;
            DateTime StartOfWeek = DateTime.Today.AddDays(-(int)DateTime.Today.DayOfWeek);
            DateTime EndOfLastWeek = StartOfWeek.AddDays(-1);
            var mondayLastWeek = StartOfWeek.AddDays(-6);
            var fridayLastWeek = StartOfWeek.AddDays(-2);
            var sundayLastWeek = StartOfWeek;
            string lastWeekMonSun = mondayLastWeek.ToString("dd/MM/yyyy") + "|" + sundayLastWeek.ToString("dd/MM/yyyy");
            string lastWeekMonFri = mondayLastWeek.ToString("dd/MM/yyyy") + "|" + fridayLastWeek.ToString("dd/MM/yyyy");
            string sLast30Days = DateTime.Now.AddDays(-29).ToString("dd/MM/yyyy") + "|" + ToDay;
            DateTime firstDayOfThisMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
            DateTime lastDayOfThisMonth = firstDayOfThisMonth.AddMonths(1).AddDays(-1);
            DateTime firstDayOfLastMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(-1);
            DateTime lastDayOfLastMonth = firstDayOfLastMonth.AddMonths(1).AddDays(-1);
            string thisMonth = firstDayOfThisMonth.ToString("dd/MM/yyyy") + "|" + ToDay;
            string lastMonth = firstDayOfLastMonth.ToString("dd/MM/yyyy") + "|" + lastDayOfLastMonth.ToString("dd/MM/yyyy");

            listRange.Add(new ItemFilter { ID = today, ItemName = "Hôm nay", CssName = "btn-today" });
            listRange.Add(new ItemFilter { ID = yesterday, ItemName = "Hôm qua", CssName = "btn-yesterday" });
            listRange.Add(new ItemFilter { ID = sLast7Days, ItemName = "7 ngày qua", CssName = "btn-last7days" });
            listRange.Add(new ItemFilter { ID = lastWeekMonSun, ItemName = "Tuần trước (Thứ 2-Chủ nhật)", CssName = "btn-lastweeksun" });
            listRange.Add(new ItemFilter { ID = lastWeekMonFri, ItemName = "Tuần trước (Thứ 2-Thứ 6)", CssName = "btn-lastweekfri" });
            listRange.Add(new ItemFilter { ID = sLast30Days, ItemName = "30 ngày qua", CssName = "btn-last30days" });
            listRange.Add(new ItemFilter { ID = lastMonth, ItemName = "Tháng trước", CssName = "btn-lastmonth" });
            listRange.Add(new ItemFilter { ID = thisMonth, ItemName = "Tháng này", CssName = "btn-thismonth" });
            listRange.Add(new ItemFilter { ID = "-1", ItemName = "Tất cả", CssName = "btn-alltime" });

            return listRange;
        }

        public List<ItemFilter> GetRangeList()
        {
            List<ItemFilter> listRange = new List<ItemFilter>();
            string ToDay = String.Format("{0:dd/MM/yyyy}", DateTime.Now);
            string Yesterday = DateTime.Now.AddDays(-1).ToString("dd/MM/yyyy");
            string today = ToDay + "|" + ToDay;
            string yesterday = Yesterday + "|" + Yesterday;
            string sLast7Days = DateTime.Now.AddDays(-7).ToString("dd/MM/yyyy") + "|" + Yesterday;
            DateTime StartOfWeek = DateTime.Today.AddDays(-(int)DateTime.Today.DayOfWeek);
            DateTime EndOfLastWeek = StartOfWeek.AddDays(-1);
            var mondayLastWeek = StartOfWeek.AddDays(-6);
            var fridayLastWeek = StartOfWeek.AddDays(-2);
            var sundayLastWeek = StartOfWeek;
            string lastWeekMonSun = mondayLastWeek.ToString("dd/MM/yyyy") + "|" + sundayLastWeek.ToString("dd/MM/yyyy");
            string lastWeekMonFri = mondayLastWeek.ToString("dd/MM/yyyy") + "|" + fridayLastWeek.ToString("dd/MM/yyyy");
            string sLast30Days = DateTime.Now.AddDays(-30).ToString("dd/MM/yyyy") + "|" + Yesterday;
            string sLast60Days = DateTime.Now.AddDays(-60).ToString("dd/MM/yyyy") + "|" + Yesterday;
            string sLast90Days = DateTime.Now.AddDays(-90).ToString("dd/MM/yyyy") + "|" + Yesterday;
            string sLast120Days = DateTime.Now.AddDays(-120).ToString("dd/MM/yyyy") + "|" + Yesterday;
            DateTime firstDayOfThisMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
            DateTime lastDayOfThisMonth = firstDayOfThisMonth.AddMonths(1).AddDays(-1);
            DateTime firstDayOfLastMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(-1);
            DateTime lastDayOfLastMonth = firstDayOfLastMonth.AddMonths(1).AddDays(-1);
            string thisMonth = firstDayOfThisMonth.ToString("dd/MM/yyyy") + "|" + ToDay;
            string lastMonth = firstDayOfLastMonth.ToString("dd/MM/yyyy") + "|" + lastDayOfLastMonth.ToString("dd/MM/yyyy");
            ItemFilter item1 = new ItemFilter { ID = today, ItemName = "Hôm nay" };
            ItemFilter item2 = new ItemFilter { ID = yesterday, ItemName = "Hôm qua" };
            ItemFilter item3 = new ItemFilter { ID = sLast7Days, ItemName = "7 ngày trước" };
            ItemFilter item4 = new ItemFilter { ID = lastWeekMonSun, ItemName = "Tuần trước (Thứ 2-CN)" };
            ItemFilter item5 = new ItemFilter { ID = lastWeekMonFri, ItemName = "Tuần trước (Thứ 2-thứ 6)" };
            ItemFilter item6 = new ItemFilter { ID = sLast30Days, ItemName = "30 ngày trước" };
            ItemFilter item7 = new ItemFilter { ID = sLast60Days, ItemName = "60 ngày trước" };
            ItemFilter item8 = new ItemFilter { ID = sLast90Days, ItemName = "90 ngày trước" };
            ItemFilter item9 = new ItemFilter { ID = sLast120Days, ItemName = "120 ngày trước" };
            ItemFilter item10 = new ItemFilter { ID = thisMonth, ItemName = "Tháng này" };
            ItemFilter item11 = new ItemFilter { ID = lastMonth, ItemName = "Tháng trước" };

            listRange.Add(item1);
            listRange.Add(item2);
            listRange.Add(item3);
            listRange.Add(item4);
            listRange.Add(item5);
            listRange.Add(item6);
            listRange.Add(item7);
            listRange.Add(item8);
            listRange.Add(item9);
            listRange.Add(item10);
            listRange.Add(item11);

            return listRange;
        }

        public List<ItemFilter> GetDuration()
        {
            List<ItemFilter> data = new List<ItemFilter>();
            Dictionary<string, string> s = new Dictionary<string, string>()
			{
				{"15", "15 phút"},
				{"30", "30 phút"},
				{"45", "45 phút"},
				{"60", "1 giờ"},
				{"75", "1 giờ 15"},
				{"90", "1 giờ 30"},
				{"120", "2 giờ"},
				{"180", "3 giờ"}
			};

            foreach (var item in s)
            {
                ItemFilter filter = new ItemFilter();
                filter.ID = item.Key;
                filter.ItemName = item.Value;
                data.Add(filter);
            }

            return data;
        }
        
        public string GetOLEDBConnectionString(string path)
        {
            string conn;

            path = path.ToLower();

            if (path.EndsWith(".csv"))
                conn = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Text;HDR=YES;FMT=Delimited\"", Path.GetDirectoryName(path));
            else
                conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";

            return conn;
        }

        public DataTable GetDataTable(string path)
        {
            DataTable dt = null;
            string connString = GetOLEDBConnectionString(path);

            OleDbConnection oledbConn = new OleDbConnection(connString);
            try
            {
                oledbConn.Open();
                dt = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                {
                    return dt;
                }

                String[] excelSheets = new String[dt.Rows.Count];
                int i = 0;

                // Add the sheet name to the string array.
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    i++;
                }

                // Execute a query to read data
                OleDbCommand cmd;
                if (path.ToLower().EndsWith(".csv"))
                    cmd = new OleDbCommand("SELECT * FROM [" + Path.GetFileName(path) + "]", oledbConn);
                else
                    cmd = new OleDbCommand("SELECT * FROM [" + excelSheets[0] + "]", oledbConn);
                OleDbDataAdapter oleda = new OleDbDataAdapter();
                oleda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                oleda.Fill(ds);
                dt = ds.Tables[0];
            }
            catch
            {
                throw;
            }
            finally
            {
                oledbConn.Close();
            }

            return dt;
        }

        public List<string> GetAllColumnName()
        {
            List<string> list = new List<string>();

            // Execute query to get list of column name on leadwarehouse table
            using (SqlDataReader item = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringProfile, CommandType.StoredProcedure, "GetAllLeadWarehouseColumnName"))
            {
                while (item.Read())
                {
                    list.Add(item.GetString(0));
                }
            }

            return list;
        }

        public List<RegularExpressionItem> GetAllRegularExpression()
        {
            List<RegularExpressionItem> list = new List<RegularExpressionItem>();

            // 01. Text
            list.Add(new RegularExpressionItem() { TypeId = 1, FielTypeName = "Text", Expression = @"^[a-zA-Z0-9]+$", ClientExpression = @"^[a-zA-Z0-9]+$" });

            // 02. Number
            list.Add(new RegularExpressionItem() { TypeId = 2, FielTypeName = "Number", Expression = @"^[0-9, ]*\.?[0-9 ]*$", ClientExpression = @"^[0-9, ]*\\.?[0-9 ]*$" });

            // 04. Money
            list.Add(new RegularExpressionItem() { TypeId = 4, FielTypeName = "Money", Expression = @"^ *\$?[0-9, ]*\.?[0-9 ]*$", ClientExpression = @"^ *\\$?[0-9, ]*\\.?[0-9 ]*$" });

            // 08. Date
            list.Add(new RegularExpressionItem() { TypeId = 8, FielTypeName = "Date", Expression = @"^(0?[1-9]|1[012])/(0?[1-9]|[12][0-9]|3[01])/(19\d\d|(20)?[0-7][0-8])$", ClientExpression = @"^(0?[1-9]|[12][0-9]|3[01])/(0?[1-9]|1[012])/(19\\d\\d|(20)?[0-7][0-8])$" });

            // 16. Phone
            list.Add(new RegularExpressionItem() { TypeId = 16, FielTypeName = "Phone", Expression = @"^[0-9x -\.\(\)]{10,}$", ClientExpression = @"^[0-9x -\\.\\(\\)]{10,}$" });

            // 32. State
            list.Add(new RegularExpressionItem() { TypeId = 32, FielTypeName = "State", Expression = "", ClientExpression="" });

            // 64. Dropdown
            list.Add(new RegularExpressionItem() { TypeId = 64, FielTypeName = "Dropdown", Expression = "", ClientExpression = "" });

            // 128. Zip Code
            list.Add(new RegularExpressionItem() { TypeId = 128, FielTypeName = "Zip Code", Expression = @"(^\d{5}$)|(^\d{5}\-?\d{4}$)", ClientExpression = @"(^\\d{5}$)|(^\\d{5}\-?\\d{4}$)" });

            // 256. Long Text
            list.Add(new RegularExpressionItem() { TypeId = 256, FielTypeName = "Long Text", Expression = "", ClientExpression = "" });

            // 512. Checkbox
            list.Add(new RegularExpressionItem() { TypeId = 512, FielTypeName = "Checkbox", Expression = "", ClientExpression = "" });

            // 1024. Radio Buttons
            list.Add(new RegularExpressionItem() { TypeId = 1024, FielTypeName = "Radio Buttons", Expression = "", ClientExpression = "" });

            // 2048. Email
            list.Add(new RegularExpressionItem() { TypeId = 2048, FielTypeName = "Email", Expression = @"^([\w-]+(?:\.[\w-]+)*)@((?:[\w-]+\.)*\w[\w-]{0,66})\.([a-z]{2,6}(?:\.[a-z]{2})?)$", ClientExpression = @"^([\w-]+(?:\.[\w-]+)*)@((?:[\w-]+\.)*\w[\w-]{0,66})\.([a-z]{2,6}(?:\.[a-z]{2})?)$" });

            // 4096. SSN
            list.Add(new RegularExpressionItem() { TypeId = 4096, FielTypeName = "SSN", Expression = @"^[0-9]{3}\-?[0-9]{2}\-?[0-9]{4}", ClientExpression = @"^[0-9]{3}\-?[0-9]{2}\-?[0-9]{4}" });

            // 8192. Date/Time
            list.Add(new RegularExpressionItem() { TypeId = 8192, FielTypeName = "Date/Time", Expression = @"/[0-1]\d\/[0-2]\d|[3][01]\/\d{4}|\d{2} [0-1]\d:[0-5]\d [AP][M]/", ClientExpression = @"/[0-1]\\d\\/[0-2]\\d|[3][01]\\/\\d{4}|\\d{2} [0-1]\\d:[0-5]\\d [AP][M]/" });

            // 16384. Time
            list.Add(new RegularExpressionItem() { TypeId = 16384, FielTypeName = "Time", Expression = @"^([0-1]?[0-9]|2[0-4]):([0-5][0-9])(:[0-5][0-9])? ([pa]m|[PA]M)$", ClientExpression = @"^([0-1]?[0-9]|2[0-4]):([0-5][0-9])(:[0-5][0-9])? ([pa]m|[PA]M)$" });

            // 32768. Formula
            list.Add(new RegularExpressionItem() { TypeId = 32768, FielTypeName = "Formula", Expression = "", ClientExpression = "" });

            return list;
        }
    }
}